---
title: Query Examples
description: "Common SQL patterns for analytics and billing"
---

This guide provides SQL query examples for common analytics scenarios covering all the use cases from the legacy API and more. All examples use ClickHouse SQL syntax and work with the `/v2/analytics.getVerifications` endpoint.

## Using Queries in API Requests

When making API requests, you need to format the SQL query as a JSON string on a single line. Here's how:

<CodeGroup>
```sql SQL
SELECT COUNT(*) as total
FROM key_verifications_v1
WHERE time >= now() - INTERVAL 7 DAY
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT COUNT(*) as total FROM key_verifications_v1 WHERE time >= now() - INTERVAL 7 DAY"
  }'
```

</CodeGroup>

<Tip>
  Each example below shows both the readable multi-line SQL and the single-line
  JSON format you can copy directly into your API requests.
</Tip>

## Usage Analytics

**Use this for:** High-level usage metrics, health monitoring, and trend analysis.

**Key patterns:** Total counts, outcome breakdowns, time series analysis.

### Total verifications in the last 7 days

Count total verifications across all APIs in the last 7 days.

<CodeGroup>
```sql SQL
SELECT SUM(count) as total_verifications
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 7 DAY
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT SUM(count) as total_verifications FROM key_verifications_per_day_v1 WHERE time >= now() - INTERVAL 7 DAY"
  }'
```

</CodeGroup>

### Verifications by outcome

Break down verifications by outcome to understand success vs failure rates.

<CodeGroup>
```sql SQL
SELECT
  outcome,
  SUM(count) as count
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 30 DAY
GROUP BY outcome
ORDER BY count DESC
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT outcome, SUM(count) as count FROM key_verifications_per_day_v1 WHERE time >= now() - INTERVAL 30 DAY GROUP BY outcome ORDER BY count DESC"
  }'
```

</CodeGroup>

### All outcomes in a single row

Get all verification outcomes in one row with individual columns for each outcome type.

<CodeGroup>
```sql SQL
SELECT
  sumIf(count, outcome = 'VALID') AS valid,
  sumIf(count, outcome = 'RATE_LIMITED') AS rateLimited,
  sumIf(count, outcome = 'INVALID') AS invalid,
  sumIf(count, outcome = 'NOT_FOUND') AS notFound,
  sumIf(count, outcome = 'FORBIDDEN') AS forbidden,
  sumIf(count, outcome = 'USAGE_EXCEEDED') AS usageExceeded,
  sumIf(count, outcome = 'UNAUTHORIZED') AS unauthorized,
  sumIf(count, outcome = 'DISABLED') AS disabled,
  sumIf(count, outcome = 'INSUFFICIENT_PERMISSIONS') AS insufficientPermissions,
  sumIf(count, outcome = 'EXPIRED') AS expired,
  SUM(count) AS total
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 30 DAY
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT sumIf(count, outcome = '\''VALID'\'') AS valid, sumIf(count, outcome = '\''RATE_LIMITED'\'') AS rateLimited, sumIf(count, outcome = '\''INVALID'\'') AS invalid, sumIf(count, outcome = '\''NOT_FOUND'\'') AS notFound, sumIf(count, outcome = '\''FORBIDDEN'\'') AS forbidden, sumIf(count, outcome = '\''USAGE_EXCEEDED'\'') AS usageExceeded, sumIf(count, outcome = '\''UNAUTHORIZED'\'') AS unauthorized, sumIf(count, outcome = '\''DISABLED'\'') AS disabled, sumIf(count, outcome = '\''INSUFFICIENT_PERMISSIONS'\'') AS insufficientPermissions, sumIf(count, outcome = '\''EXPIRED'\'') AS expired, SUM(count) AS total FROM key_verifications_per_day_v1 WHERE time >= now() - INTERVAL 30 DAY"
  }'
```

</CodeGroup>

### All outcomes per key

Get outcome breakdown for each API key in a single row per key.

<CodeGroup>
```sql SQL
SELECT
  key_id,
  sumIf(count, outcome = 'VALID') AS valid,
  sumIf(count, outcome = 'RATE_LIMITED') AS rateLimited,
  sumIf(count, outcome = 'INVALID') AS invalid,
  sumIf(count, outcome = 'NOT_FOUND') AS notFound,
  sumIf(count, outcome = 'FORBIDDEN') AS forbidden,
  sumIf(count, outcome = 'USAGE_EXCEEDED') AS usageExceeded,
  sumIf(count, outcome = 'UNAUTHORIZED') AS unauthorized,
  sumIf(count, outcome = 'DISABLED') AS disabled,
  sumIf(count, outcome = 'INSUFFICIENT_PERMISSIONS') AS insufficientPermissions,
  sumIf(count, outcome = 'EXPIRED') AS expired,
  SUM(count) AS total
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 30 DAY
GROUP BY key_id
ORDER BY total DESC
LIMIT 100
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT key_id, sumIf(count, outcome = '\''VALID'\'') AS valid, sumIf(count, outcome = '\''RATE_LIMITED'\'') AS rateLimited, sumIf(count, outcome = '\''INVALID'\'') AS invalid, sumIf(count, outcome = '\''NOT_FOUND'\'') AS notFound, sumIf(count, outcome = '\''FORBIDDEN'\'') AS forbidden, sumIf(count, outcome = '\''USAGE_EXCEEDED'\'') AS usageExceeded, sumIf(count, outcome = '\''UNAUTHORIZED'\'') AS unauthorized, sumIf(count, outcome = '\''DISABLED'\'') AS disabled, sumIf(count, outcome = '\''INSUFFICIENT_PERMISSIONS'\'') AS insufficientPermissions, sumIf(count, outcome = '\''EXPIRED'\'') AS expired, SUM(count) AS total FROM key_verifications_per_day_v1 WHERE time >= now() - INTERVAL 30 DAY GROUP BY key_id ORDER BY total DESC LIMIT 100"
  }'
```

</CodeGroup>

### Daily verification trend

Track daily verification patterns over the last 30 days.

<CodeGroup>
```sql SQL
SELECT
  time as date,
  SUM(count) as verifications
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 30 DAY
GROUP BY date
ORDER BY date
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT time as date, SUM(count) as verifications FROM key_verifications_per_day_v1 WHERE time >= now() - INTERVAL 30 DAY GROUP BY date ORDER BY date"
  }'
```

</CodeGroup>

### Hourly breakdown for today

Analyze hourly verification patterns for today with outcome breakdown.

<CodeGroup>
```sql SQL
SELECT
  time as hour,
  outcome,
  SUM(count) as verifications
FROM key_verifications_per_hour_v1
WHERE time >= toStartOfDay(now())
GROUP BY time, outcome
ORDER BY time, outcome
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT time as hour, outcome, SUM(count) as verifications FROM key_verifications_per_hour_v1 WHERE time >= toStartOfDay(now()) GROUP BY time, outcome ORDER BY time, outcome"
  }'
```

</CodeGroup>

## Usage by User

**Use this for:** Understanding user behavior, identifying power users, tracking user activity over time.

**Key patterns:** User ranking, activity trends, specific user analysis.

### All users ranked by usage

Rank all users by their total verification usage over the last 30 days.

<CodeGroup>
```sql SQL
SELECT
  external_id,
  SUM(count) as total_verifications,
  SUM(CASE WHEN outcome = 'VALID' THEN count ELSE 0 END) as successful,
  SUM(CASE WHEN outcome = 'RATE_LIMITED' THEN count ELSE 0 END) as rate_limited
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 30 DAY
  AND external_id != ''
GROUP BY external_id
ORDER BY total_verifications DESC
LIMIT 100
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT external_id, SUM(count) as total_verifications, SUM(CASE WHEN outcome = '\''VALID'\'' THEN count ELSE 0 END) as successful, SUM(CASE WHEN outcome = '\''RATE_LIMITED'\'' THEN count ELSE 0 END) as rate_limited FROM key_verifications_per_day_v1 WHERE time >= now() - INTERVAL 30 DAY AND external_id != '\'''\'' GROUP BY external_id ORDER BY total_verifications DESC LIMIT 100"
  }'
```

</CodeGroup>

### Usage for a specific user

Analyze usage patterns for a specific user over the last 30 days.

<CodeGroup>
```sql SQL
SELECT
  SUM(count) as total_verifications,
  SUM(CASE WHEN outcome = 'VALID' THEN count ELSE 0 END) as successful,
  SUM(CASE WHEN outcome = 'RATE_LIMITED' THEN count ELSE 0 END) as rate_limited
FROM key_verifications_per_day_v1
WHERE external_id = 'user_123'
  AND time >= now() - INTERVAL 30 DAY
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT SUM(count) as total_verifications, SUM(CASE WHEN outcome = '\''VALID'\'' THEN count ELSE 0 END) as successful, SUM(CASE WHEN outcome = '\''RATE_LIMITED'\'' THEN count ELSE 0 END) as rate_limited FROM key_verifications_per_day_v1 WHERE external_id = '\''user_123'\'' AND time >= now() - INTERVAL 30 DAY"
  }'
```

</CodeGroup>

### Top 10 users by API usage

Identify your most active users by verification count.

<CodeGroup>
```sql SQL
SELECT
  external_id,
  SUM(count) as total_verifications
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 30 DAY
  AND external_id != ''
GROUP BY external_id
ORDER BY total_verifications DESC
LIMIT 10
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT external_id, SUM(count) as total_verifications FROM key_verifications_per_day_v1 WHERE time >= now() - INTERVAL 30 DAY AND external_id != '\'''\'' GROUP BY external_id ORDER BY total_verifications DESC LIMIT 10"
  }'
```

</CodeGroup>

### Daily usage per user

Track daily verification patterns for each user over 30 days.

<CodeGroup>
```sql SQL
SELECT
  external_id,
  time as date,
  SUM(count) as verifications
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 30 DAY
GROUP BY external_id, date
ORDER BY external_id, date
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT external_id, time as date, SUM(count) as verifications FROM key_verifications_per_day_v1 WHERE time >= now() - INTERVAL 30 DAY GROUP BY external_id, date ORDER BY external_id, date"
  }'
```

</CodeGroup>

## API Analytics

**Use this for:** Comparing API performance, usage across different APIs, API-specific analysis.

**Key patterns:** API comparison, success rates, per-API breakdowns.

### Usage per API

Compare usage across all APIs to identify most active endpoints.

<CodeGroup>
```sql SQL
SELECT
  key_space_id,
  SUM(count) as total_verifications,
  SUM(CASE WHEN outcome = 'VALID' THEN count ELSE 0 END) as successful
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 30 DAY
GROUP BY key_space_id
ORDER BY total_verifications DESC
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT key_space_id, SUM(count) as total_verifications, SUM(CASE WHEN outcome = '\''VALID'\'' THEN count ELSE 0 END) as successful FROM key_verifications_per_day_v1 WHERE time >= now() - INTERVAL 30 DAY GROUP BY key_space_id ORDER BY total_verifications DESC"
  }'
```

</CodeGroup>

### Usage for a specific API

Analyze detailed usage patterns for a specific API over 30 days.

<CodeGroup>
```sql SQL
SELECT
  SUM(count) as total_verifications,
  SUM(CASE WHEN outcome = 'VALID' THEN count ELSE 0 END) as successful,
  SUM(CASE WHEN outcome = 'RATE_LIMITED' THEN count ELSE 0 END) as rate_limited,
  SUM(CASE WHEN outcome = 'INVALID' THEN count ELSE 0 END) as invalid
FROM key_verifications_per_day_v1
WHERE key_space_id = 'ks_1234'
  AND time >= now() - INTERVAL 30 DAY
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT SUM(count) as total_verifications, SUM(CASE WHEN outcome = '\''VALID'\'' THEN count ELSE 0 END) as successful, SUM(CASE WHEN outcome = '\''RATE_LIMITED'\'' THEN count ELSE 0 END) as rate_limited, SUM(CASE WHEN outcome = '\''INVALID'\'' THEN count ELSE 0 END) as invalid FROM key_verifications_per_day_v1 WHERE key_space_id = '\''ks_1234'\'' AND time >= now() - INTERVAL 30 DAY"
  }'
```

</CodeGroup>

### Compare multiple APIs

Calculate success rates for multiple APIs to compare performance.

<CodeGroup>
```sql SQL
SELECT
  key_space_id,
  SUM(count) as verifications,
  round(SUM(CASE WHEN outcome = 'VALID' THEN count ELSE 0 END) / SUM(count) * 100, 2) as success_rate
FROM key_verifications_per_day_v1
WHERE key_space_id IN ('ks_1234', 'ks_5678')
  AND time >= now() - INTERVAL 7 DAY
GROUP BY key_space_id
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT key_space_id, SUM(count) as verifications, round(SUM(CASE WHEN outcome = '\''VALID'\'' THEN count ELSE 0 END) / SUM(count) * 100, 2) as success_rate FROM key_verifications_per_day_v1 WHERE key_space_id IN ('\''ks_1234'\'', '\''ks_5678'\'') AND time >= now() - INTERVAL 7 DAY GROUP BY key_space_id"
  }'
```

</CodeGroup>

## Key Analytics

**Use this for:** Individual API key analysis, identifying problematic keys, key-specific usage patterns.

**Key patterns:** Key ranking, error analysis, specific key monitoring.

### Usage per key

Identify your most frequently used API keys over the last 30 days.

<CodeGroup>
```sql SQL
SELECT
  key_id,
  SUM(count) as total_verifications,
  SUM(CASE WHEN outcome = 'VALID' THEN count ELSE 0 END) as successful
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 30 DAY
GROUP BY key_id
ORDER BY total_verifications DESC
LIMIT 100
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT key_id, SUM(count) as total_verifications, SUM(CASE WHEN outcome = '\''VALID'\'' THEN count ELSE 0 END) as successful FROM key_verifications_per_day_v1 WHERE time >= now() - INTERVAL 30 DAY GROUP BY key_id ORDER BY total_verifications DESC LIMIT 100"
  }'
```

</CodeGroup>

### Usage for a specific key

Analyze detailed usage patterns for a specific API key.

<CodeGroup>
```sql SQL
SELECT
  SUM(count) as total_verifications,
  SUM(CASE WHEN outcome = 'VALID' THEN count ELSE 0 END) as successful,
  SUM(CASE WHEN outcome = 'RATE_LIMITED' THEN count ELSE 0 END) as rate_limited
FROM key_verifications_per_day_v1
WHERE key_id = 'key_1234'
  AND time >= now() - INTERVAL 30 DAY
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT SUM(count) as total_verifications, SUM(CASE WHEN outcome = '\''VALID'\'' THEN count ELSE 0 END) as successful, SUM(CASE WHEN outcome = '\''RATE_LIMITED'\'' THEN count ELSE 0 END) as rate_limited FROM key_verifications_per_day_v1 WHERE key_id = '\''key_1234'\'' AND time >= now() - INTERVAL 30 DAY"
  }'
```

</CodeGroup>

### Keys with most errors

Find API keys that are generating the most errors.

<CodeGroup>
```sql SQL
SELECT
  key_id,
  SUM(count) as total_errors,
  groupArray(DISTINCT outcome) as error_types
FROM key_verifications_per_day_v1
WHERE outcome != 'VALID'
  AND time >= now() - INTERVAL 7 DAY
GROUP BY key_id
ORDER BY total_errors DESC
LIMIT 20
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT key_id, SUM(count) as total_errors, groupArray(DISTINCT outcome) as error_types FROM key_verifications_per_day_v1 WHERE outcome != '\''VALID'\'' AND time >= now() - INTERVAL 7 DAY GROUP BY key_id ORDER BY total_errors DESC LIMIT 20"
  }'
```

</CodeGroup>

## Tag-Based Analytics

**Use this for:** Custom metadata filtering, endpoint analysis, user segmentation using tags.

**Key patterns:** Tag filtering, endpoint breakdowns, custom attribute analysis.

Tags allow you to add custom metadata to verification requests for filtering and aggregation.

### Filter by single tag

Count verifications for requests with a specific tag.

<CodeGroup>
```sql SQL
SELECT SUM(count) as total
FROM key_verifications_per_day_v1
WHERE has(tags, 'path=/api/v1/users')
  AND time >= now() - INTERVAL 7 DAY
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT SUM(count) as total FROM key_verifications_per_day_v1 WHERE has(tags, '\''path=/api/v1/users'\'') AND time >= now() - INTERVAL 7 DAY"
  }'
```

</CodeGroup>

### Filter by multiple tags (OR)

Count verifications matching any of multiple tags.

<CodeGroup>
```sql SQL
SELECT SUM(count) as total
FROM key_verifications_per_day_v1
WHERE hasAny(tags, ['path=/api/v1/users', 'path=/api/v1/posts'])
  AND time >= now() - INTERVAL 7 DAY
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT SUM(count) as total FROM key_verifications_per_day_v1 WHERE hasAny(tags, ['\''path=/api/v1/users'\'', '\''path=/api/v1/posts'\'']) AND time >= now() - INTERVAL 7 DAY"
  }'
```

</CodeGroup>

### Filter by multiple tags (AND)

Count verifications matching all specified tags.

<CodeGroup>
```sql SQL
SELECT SUM(count) as total
FROM key_verifications_per_day_v1
WHERE hasAll(tags, ['environment=production', 'team=backend'])
  AND time >= now() - INTERVAL 7 DAY
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT SUM(count) as total FROM key_verifications_per_day_v1 WHERE hasAll(tags, ['\''environment=production'\'', '\''team=backend'\'']) AND time >= now() - INTERVAL 7 DAY"
  }'
```

</CodeGroup>

### Group by tag

Aggregate verifications by individual tags to see usage patterns.

<CodeGroup>
```sql SQL
SELECT
  arrayJoin(tags) as tag,
  SUM(count) as verifications
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 7 DAY
GROUP BY tag
ORDER BY verifications DESC
LIMIT 20
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT arrayJoin(tags) as tag, SUM(count) as verifications FROM key_verifications_per_day_v1 WHERE time >= now() - INTERVAL 7 DAY GROUP BY tag ORDER BY verifications DESC LIMIT 20"
  }'
```

</CodeGroup>

### Breakdown by endpoint (using path tag)

Analyze request volume by API endpoint over the last 24 hours.

<Note>
  This query uses the raw table for detailed tag analysis. For longer time
  ranges, consider using aggregated tables and pre-filtered tags.
</Note>

<CodeGroup>
```sql SQL
SELECT
  arrayJoin(arrayFilter(x -> startsWith(x, 'path='), tags)) as endpoint,
  COUNT(*) as requests
FROM key_verifications_v1
WHERE time >= now() - INTERVAL 24 HOUR
GROUP BY endpoint
ORDER BY requests DESC
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT arrayJoin(arrayFilter(x -> startsWith(x, '\''path='\''), tags)) as endpoint, COUNT(*) as requests FROM key_verifications_v1 WHERE time >= now() - INTERVAL 24 HOUR GROUP BY endpoint ORDER BY requests DESC"
  }'
```

</CodeGroup>

## Billing & Usage-Based Pricing

**Use this for:** Usage-based billing implementation, credit tracking, user tier calculation.

**Key patterns:** Credit aggregation, billing cycles, tier determination, cost analysis.

### Monthly credits per user

Calculate monthly credit consumption per user for billing.

<CodeGroup>
```sql SQL
SELECT
  external_id,
  toStartOfMonth(time) as month,
  SUM(spent_credits) as total_credits
FROM key_verifications_per_day_v1
WHERE external_id != ''
  AND time >= toStartOfMonth(now())
GROUP BY external_id, month
ORDER BY total_credits DESC
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT external_id, toStartOfMonth(time) as month, SUM(spent_credits) as total_credits FROM key_verifications_per_day_v1 WHERE external_id != '\'''\'' AND time >= toStartOfMonth(now()) GROUP BY external_id, month ORDER BY total_credits DESC"
  }'
```

</CodeGroup>

### Current billing period credits

Calculate credit usage for a specific billing period.

<CodeGroup>
```sql SQL
SELECT
  external_id,
  SUM(spent_credits) as credits_this_period
FROM key_verifications_per_day_v1
WHERE external_id = 'user_123'
  AND time >= 1704067200000  -- Start of billing period (Unix millis)
  AND time < 1706745600000   -- End of billing period (Unix millis)
GROUP BY external_id
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT external_id, SUM(spent_credits) as credits_this_period FROM key_verifications_per_day_v1 WHERE external_id = '\''user_123'\'' AND time >= 1704067200000 AND time < 1706745600000 GROUP BY external_id"
  }'
```

</CodeGroup>

### Credit-based tier calculation

Determine user tiers based on monthly credit consumption.

<CodeGroup>
```sql SQL
SELECT
  external_id,
  SUM(spent_credits) as total_credits,
  CASE
    WHEN total_credits <= 1000 THEN 'free'
    WHEN total_credits <= 10000 THEN 'starter'
    WHEN total_credits <= 100000 THEN 'pro'
    ELSE 'enterprise'
  END as tier
FROM key_verifications_per_day_v1
WHERE time >= toStartOfMonth(now())
  AND external_id = 'user_123'
GROUP BY external_id
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT external_id, SUM(spent_credits) as total_credits, CASE WHEN total_credits <= 1000 THEN '\''free'\'' WHEN total_credits <= 10000 THEN '\''starter'\'' WHEN total_credits <= 100000 THEN '\''pro'\'' ELSE '\''enterprise'\'' END as tier FROM key_verifications_per_day_v1 WHERE time >= toStartOfMonth(now()) AND external_id = '\''user_123'\'' GROUP BY external_id"
  }'
```

</CodeGroup>

### Daily credit usage and cost

Track daily credit consumption and calculate estimated costs.

<CodeGroup>
```sql SQL
SELECT
  time as date,
  SUM(spent_credits) as credits_used,
  credits_used * 0.001 as estimated_cost  -- $0.001 per credit
FROM key_verifications_per_day_v1
WHERE external_id = 'user_123'
  AND time >= now() - INTERVAL 30 DAY
GROUP BY date
ORDER BY date
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT time as date, SUM(spent_credits) as credits_used, credits_used * 0.001 as estimated_cost FROM key_verifications_per_day_v1 WHERE external_id = '\''user_123'\'' AND time >= now() - INTERVAL 30 DAY GROUP BY date ORDER BY date"
  }'
```

</CodeGroup>

## Advanced Queries

**Use this for:** Complex analytical patterns, cohort analysis, moving averages, advanced insights.

**Key patterns:** User retention, trend smoothing, complex joins, window functions.

### Cohort analysis: New vs returning users

Perform cohort analysis to understand user retention patterns.

<CodeGroup>
```sql SQL
WITH first_seen AS (
  SELECT
    external_id,
    min(time) as first_verification
  FROM key_verifications_per_day_v1
  WHERE external_id != ''
  GROUP BY external_id
)
SELECT
  toDate(kv.time) as date,
  SUM(CASE WHEN kv.time = fs.first_verification THEN kv.count ELSE 0 END) as new_users,
  SUM(CASE WHEN kv.time > fs.first_verification THEN kv.count ELSE 0 END) as returning_users
FROM key_verifications_per_day_v1 kv
JOIN first_seen fs ON kv.external_id = fs.external_id
WHERE kv.time >= now() - INTERVAL 30 DAY
GROUP BY date
ORDER BY date
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "WITH first_seen AS ( SELECT external_id, min(time) as first_verification FROM key_verifications_per_day_v1 WHERE external_id != '\'''\'' GROUP BY external_id ) SELECT toDate(kv.time) as date, SUM(CASE WHEN kv.time = fs.first_verification THEN kv.count ELSE 0 END) as new_users, SUM(CASE WHEN kv.time > fs.first_verification THEN kv.count ELSE 0 END) as returning_users FROM key_verifications_per_day_v1 kv JOIN first_seen fs ON kv.external_id = fs.external_id WHERE kv.time >= now() - INTERVAL 30 DAY GROUP BY date ORDER BY date"
  }'
```

</CodeGroup>

### Moving average (7-day)

Calculate 7-day moving average to smooth out daily fluctuations.

<CodeGroup>
```sql SQL
SELECT
  date,
  verifications,
  avg(verifications) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_avg_7d
FROM (
  SELECT
    time as date,
    SUM(count) as verifications
  FROM key_verifications_per_day_v1
  WHERE time >= now() - INTERVAL 60 DAY
  GROUP BY date
)
ORDER BY date
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT date, verifications, avg(verifications) OVER ( ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) as moving_avg_7d FROM ( SELECT time as date, SUM(count) as verifications FROM key_verifications_per_day_v1 WHERE time >= now() - INTERVAL 60 DAY GROUP BY date ) ORDER BY date"
  }'
```

</CodeGroup>

## Using Aggregated Tables

For better performance on large time ranges, use pre-aggregated tables:

### Hourly aggregates

Query hourly verification counts for the last 7 days.

<CodeGroup>
```sql SQL
SELECT
  time,
  SUM(count) as total
FROM key_verifications_per_hour_v1
WHERE time >= toStartOfHour(now() - INTERVAL 7 DAY)
GROUP BY time
ORDER BY time
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT time, SUM(count) as total FROM key_verifications_per_hour_v1 WHERE time >= toStartOfHour(now() - INTERVAL 7 DAY) GROUP BY time ORDER BY time"
  }'
```

</CodeGroup>

### Daily aggregates

Query daily verification counts for the last 30 days.

<CodeGroup>
```sql SQL
SELECT
  time,
  SUM(count) as total
FROM key_verifications_per_day_v1
WHERE time >= toStartOfDay(now() - INTERVAL 30 DAY)
GROUP BY time
ORDER BY time
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT time, SUM(count) as total FROM key_verifications_per_day_v1 WHERE time >= toStartOfDay(now() - INTERVAL 30 DAY) GROUP BY time ORDER BY time"
  }'
```

</CodeGroup>

### Monthly aggregates

Query monthly verification counts for the last year.

<CodeGroup>
```sql SQL
SELECT
  time,
  SUM(count) as total
FROM key_verifications_per_month_v1
WHERE time >= toStartOfMonth(now() - INTERVAL 12 MONTH)
GROUP BY time
ORDER BY time
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT time, SUM(count) as total FROM key_verifications_per_month_v1 WHERE time >= toStartOfMonth(now() - INTERVAL 12 MONTH) GROUP BY time ORDER BY time"
  }'
```

</CodeGroup>

## Filling Gaps in Time Series (WITH FILL)

When querying time series data, you may have periods with no activity that result in missing time points. ClickHouse's `WITH FILL` clause ensures all time periods are included in results, filling gaps with zeros.

<Note>
  `WITH FILL` is particularly useful for creating charts and visualizations where you need consistent time intervals, even when there's no data for some periods.
</Note>

<Warning>
  `WITH FILL` only works when grouping by the time column alone. To include outcome breakdowns or other dimensions, use `sumIf()` to pivot them into columns (see the last example below).
</Warning>

<Note>
  **Type matching:** The `time` column type varies by table:
  - **Hourly/Minute tables**: `DateTime` - use `toStartOfHour(now() - INTERVAL N HOUR)`
  - **Daily/Monthly tables**: `Date` - use `toDate(now() - INTERVAL N DAY)` or `toDate(toStartOfMonth(...))`

  WITH FILL expressions must match the column type exactly.
</Note>

### Hourly data with gaps filled

Get hourly verification counts for the last 7 days, including hours with zero activity.

<CodeGroup>
```sql SQL
SELECT
  time,
  SUM(count) as total
FROM key_verifications_per_hour_v1
WHERE time >= toStartOfHour(now() - INTERVAL 7 DAY)
  AND time <= toStartOfHour(now())
GROUP BY time
ORDER BY time ASC
  WITH FILL
  FROM toStartOfHour(now() - INTERVAL 7 DAY)
  TO toStartOfHour(now())
  STEP INTERVAL 1 HOUR
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT time, SUM(count) as total FROM key_verifications_per_hour_v1 WHERE time >= toStartOfHour(now() - INTERVAL 7 DAY) AND time <= toStartOfHour(now()) GROUP BY time ORDER BY time ASC WITH FILL FROM toStartOfHour(now() - INTERVAL 7 DAY) TO toStartOfHour(now()) STEP INTERVAL 1 HOUR"
  }'
```

</CodeGroup>

### Daily data with gaps filled

Get daily verification counts for the last 30 days, ensuring all days are present.

<CodeGroup>
```sql SQL
SELECT
  time,
  SUM(count) as total
FROM key_verifications_per_day_v1
WHERE time >= toDate(now() - INTERVAL 30 DAY)
  AND time <= toDate(now())
GROUP BY time
ORDER BY time ASC
  WITH FILL
  FROM toDate(now() - INTERVAL 30 DAY)
  TO toDate(now())
  STEP INTERVAL 1 DAY
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT time, SUM(count) as total FROM key_verifications_per_day_v1 WHERE time >= toDate(now() - INTERVAL 30 DAY) AND time <= toDate(now()) GROUP BY time ORDER BY time ASC WITH FILL FROM toDate(now() - INTERVAL 30 DAY) TO toDate(now()) STEP INTERVAL 1 DAY"
  }'
```

</CodeGroup>

### Monthly data with gaps filled

Get monthly verification counts for the last 12 months with all months included.

<CodeGroup>
```sql SQL
SELECT
  time,
  SUM(count) as total
FROM key_verifications_per_month_v1
WHERE time >= toDate(toStartOfMonth(now() - INTERVAL 12 MONTH))
  AND time <= toDate(toStartOfMonth(now()))
GROUP BY time
ORDER BY time ASC
  WITH FILL
  FROM toDate(toStartOfMonth(now() - INTERVAL 12 MONTH))
  TO toDate(toStartOfMonth(now()))
  STEP INTERVAL 1 MONTH
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT time, SUM(count) as total FROM key_verifications_per_month_v1 WHERE time >= toDate(toStartOfMonth(now() - INTERVAL 12 MONTH)) AND time <= toDate(toStartOfMonth(now())) GROUP BY time ORDER BY time ASC WITH FILL FROM toDate(toStartOfMonth(now() - INTERVAL 12 MONTH)) TO toDate(toStartOfMonth(now())) STEP INTERVAL 1 MONTH"
  }'
```

</CodeGroup>

### Filling gaps with aggregations

For more complex queries that aggregate by outcome, use a subquery or pivot approach instead of WITH FILL with multiple GROUP BY columns.

<CodeGroup>
```sql SQL
-- Pivot outcomes into columns with all days filled
SELECT
  time,
  sumIf(count, outcome = 'VALID') as valid,
  sumIf(count, outcome = 'RATE_LIMITED') as rate_limited,
  sumIf(count, outcome = 'INVALID') as invalid,
  SUM(count) as total
FROM key_verifications_per_day_v1
WHERE time >= toDate(now() - INTERVAL 30 DAY)
  AND time <= toDate(now())
GROUP BY time
ORDER BY time ASC
  WITH FILL
  FROM toDate(now() - INTERVAL 30 DAY)
  TO toDate(now())
  STEP INTERVAL 1 DAY
```

```bash cURL
curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT time, sumIf(count, outcome = '\''VALID'\'') as valid, sumIf(count, outcome = '\''RATE_LIMITED'\'') as rate_limited, sumIf(count, outcome = '\''INVALID'\'') as invalid, SUM(count) as total FROM key_verifications_per_day_v1 WHERE time >= toDate(now() - INTERVAL 30 DAY) AND time <= toDate(now()) GROUP BY time ORDER BY time ASC WITH FILL FROM toDate(now() - INTERVAL 30 DAY) TO toDate(now()) STEP INTERVAL 1 DAY"
  }'
```

</CodeGroup>

<Note>
  `WITH FILL` only works when grouping by time alone. For outcome breakdowns, use `sumIf()` to pivot outcomes into separate columns as shown above.
</Note>

## Tips for Efficient Queries

1. **Always filter by time** - Use indexes by including time filters
2. **Use aggregated tables** - Hourly/daily/monthly tables for longer ranges
3. **Add LIMIT clauses** - Prevent returning too much data
4. **Filter before grouping** - Use WHERE instead of HAVING when possible
